﻿using System;
using System.Data;
using System.Configuration;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using MySql.Data;
using MySql.Data.MySqlClient;

/// <summary>
/// Summary description for Function
/// </summary>
/// 
namespace FunctionService
{
    public class Function
    {
        public Function()
        {
            //
            // TODO: Add constructor logic here
            //

        }

       
        /// <summary>
        /// This method is used to get the table's details based on the Id [ Primary key]
        /// </summary>
        /// <param name="tableName"> Table names</param>
        /// <param name="id"> Priamry key of that table</param>
        /// <returns> Data records based on the Table name and selected Id</returns>
        public DataSet GetTableDetails(string tableName, int id)
        {
            MySqlConnection conn = new MySqlConnection((string)ConfigurationSettings.AppSettings["LitReviewConnection"]);
            try
            {
                MySqlCommand cmd1;
                MySqlDataAdapter ad1 = new MySqlDataAdapter();
                DataSet ds = new DataSet();
                cmd1 = new MySqlCommand("Select * from " + tableName + "  where id='" + id + "'", conn);
                ad1.SelectCommand = cmd1;
                conn.Open();
                ad1.Fill(ds);
                return ds;
            }
            finally
            {
                conn.Close();
            }
        }

        public DataSet GetReferenceTableDetails(string tableName, int id)
        {
            MySqlConnection conn = new MySqlConnection((string)ConfigurationSettings.AppSettings["LitReviewConnection"]);
            try
            {
                MySqlCommand cmd1;
                MySqlDataAdapter ad1 = new MySqlDataAdapter();
                DataSet ds = new DataSet();
                cmd1 = new MySqlCommand("Select * from " + tableName + "  where ref_id='" + id + "'", conn);
                ad1.SelectCommand = cmd1;
                conn.Open();
                ad1.Fill(ds);
                return ds;
            }
            finally
            {
                conn.Close();
            }
        }
        
        /// <summary>
        /// This method is used to get the primary keys based on the stored procedure
        /// </summary>
        /// <param name="StoredprocedureName"> stored procedure name</param>
        /// <returns>Primary key in the string format </returns>
        public string GetId(string StoredprocedureName)
        {
            MySqlConnection conn = new MySqlConnection((string)ConfigurationSettings.AppSettings["LitReviewConnection"]);
            MySqlCommand cmd1;

            try
            {
                conn.Open();
                string storedProcedure = StoredprocedureName;
                cmd1 = new MySqlCommand(storedProcedure, conn);
                cmd1.CommandType = CommandType.StoredProcedure;
                string UltRefKey = cmd1.ExecuteScalar().ToString();
                return UltRefKey;
            }
            catch (Exception ex)
            {
                return ex.Message;

            }
            finally
            {
                conn.Close();

            }
        }

        public DataSet ExecuteDataset(String storedprocedure)
        {
            MySqlConnection conn = new MySqlConnection((string)ConfigurationSettings.AppSettings["LitReviewConnection"]);
            try
            {
                MySqlCommand cmd1;
                MySqlDataAdapter ad1 = new MySqlDataAdapter();
                DataSet ds = new DataSet();
                String storedProc = storedprocedure;
                cmd1 = new MySqlCommand(storedProc, conn);
                cmd1.CommandType = CommandType.StoredProcedure;
                ad1.SelectCommand = cmd1;
                conn.Open();
                ad1.Fill(ds);
                return ds;
            }
            finally
            {
                conn.Close();
            }

        }

        public DataSet GetDetails(string Querystring)
        {
            MySqlConnection conn = new MySqlConnection((string)ConfigurationSettings.AppSettings["LitReviewConnection"]);
            try
            {
                MySqlCommand cmd1;
                MySqlDataAdapter ad1 = new MySqlDataAdapter();
                DataSet ds = new DataSet();
                cmd1 = new MySqlCommand(Querystring , conn);
                ad1.SelectCommand = cmd1;
                conn.Open();
                ad1.Fill(ds);
                return ds;
            }
            finally
            {
                conn.Close();
            }
        }

        public Int32 GetExperimentFKvalue(string FKName, string ExpIdValue)
        {
            MySqlConnection conn = new MySqlConnection((string)ConfigurationSettings.AppSettings["LitReviewConnection"]);
            try
            {
                MySqlCommand cmd1;
                conn.Open();
                string query = "select" + " " + FKName + " " + "from experiment where experiment_id=" + ExpIdValue;

                cmd1 = new MySqlCommand("select" + " " + FKName + " " + "from experiment where experiment_id=" + ExpIdValue, conn);
                int ExperimentId =Convert.ToInt32(cmd1.ExecuteScalar());
                return ExperimentId;
            }
          
            finally
            {
                conn.Close();
            }
        }

        


        public Int32 GetYieldId(string ExperimentId)
        {
            MySqlConnection conn = new MySqlConnection((string)ConfigurationSettings.AppSettings["LitReviewConnection"]);
            try
            {
                MySqlCommand cmd1;
                conn.Open();
                string query = "select id from yields where experiment_id=" + ExperimentId;
                cmd1 = new MySqlCommand(query, conn);
                int ExpId = Convert.ToInt32(cmd1.ExecuteScalar());
                return ExpId;
            }

            finally
            {
                conn.Close();
            }
        }
       
    }
}